﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_Business;
namespace Business.Inspect
{
    /// <summary>
    /// 效能监察操作类
    /// </summary>
    public class Xnjc
    {
        public DataTable TaskQueryDetail_list(double dHourInEveryDay, string iid)
        {
            string strSql = string.Format(@"select 编号,岗位,经办人,
        to_char(接件时间,'yyyy-mm-dd hh24:mi:ss') 接件时间,
        to_char(提交时间,'yyyy-mm-dd hh24:mi:ss') 提交时间,
        trunc((办理时间)/{1})||'天'||abs(trunc((mod((办理时间),{1})/3600)))||'小时',
        岗位编号,
        (select m.isback
          from st_work_item_hist m
         where m.iid = 编号
           and userid = t.userid and m.wiid=t.岗位编号) isback,
         (select trunc((s.exusedtime) / {1}) || '天' ||
               abs(trunc((mod((s.exusedtime), {1}) / 3600))) || '小时'
          from st_work_item_hist s
         where s.iid = '{0}'
           and  s.wiid=t.岗位编号)  已用时间,
           (select trunc((s.extotaltime) / {1}) || '天' ||
               abs(trunc((mod((s.extotaltime), {1}) / 3600))) || '小时'
          from st_work_item_hist s
         where s.iid = '{0}'
           and  s.wiid=t.岗位编号)  最大时限                  
         from sv_work_item_list t where 编号='{0}' Order By 岗位编号", iid, dHourInEveryDay);

            //获取数据
            DataTable dtSource;
            SysParams.OAConnection().RunSql(strSql, out dtSource);
            return dtSource;
        }

        public DataTable Ajxx_list(string strUserNo, string strUserName, string userid, bool ISsystemUser)
        {
            System.Data.DataTable dtOut;
            
            string strSql = "select a.iid,a.name from st_instance a,st_workflow b where 1=1 and a.wid=b.wid and a.isdelete <>1";

            if (!string.IsNullOrEmpty(strUserNo))
            {
                strSql += (" and iid like '%" + strUserNo + "%'");
            }

            if (!string.IsNullOrEmpty(strUserName))
            {
                strSql += (" and name like '%" + strUserName + "%'");
            }

            if (!ISsystemUser)//不是系统管理员
            {
                strSql += @" and b.wname in 
                                (select t.task  from xt_query_right t  where t.userid = '" + userid + "')";
            }


            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        public DataRow inspectquerydetial_list(string id)
        {
            
            string strSql = "select (select user_name from st_user where userid=记载人) 记载人,(select user_name from st_user where userid=被告人) 被告人,(select user_name from st_user where userid=裁决人) 裁决人,";
            strSql += "(select w.wname from st_workflow w,st_instance i where i.wid=w.wid and i.iid=xt_inspect.iid and i.isdelete<>1) 流程名,";
            strSql += "(select w.step from st_work_item w where w.wiid=xt_inspect.wiid union select h.step from st_work_item_hist h where h.wiid=xt_inspect.wiid) 结点名,";
            strSql += "to_char(违规时间,'yyyy-mm-dd hh24:mi:ss') 违规时间,";
            strSql += "to_char(记载时间,'yyyy-mm-dd hh24:mi:ss') 记载时间,";
            strSql += "to_char(申诉时间,'yyyy-mm-dd hh24:mi:ss') 申诉时间,";
            strSql += "to_char(裁决时间,'yyyy-mm-dd hh24:mi:ss') 裁决时间,";
            strSql += "违规事实,事件类型,记载描述,申诉描述,裁决描述,是否成立,id,wiid  from xt_inspect where id=" + id;

            System.Data.DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            System.Data.DataRow rs = dtOut.Rows[0];
            return rs;
        }


        public DataTable insurgedetail_list(string id, string step)
        {
            string sql1 = string.Format(@"select (select user_name from st_user where userid=k.userid) username,
                               to_char(exbeginmonitor, 'YYYY-MM-DD HH:MM:SS'),
                               trunc(exusedtime / {2}) || '天' ||
                               trunc((mod(exusedtime, {2}) / 3600)) || '小时' ||
                               trunc((mod(exusedtime, 3600) /60)) || '分钟',
                               exusedtime,extotaltime
                          from sv_work_step k
                         where iid = '{0}' and step = '{1}' and rownum<2 order by wiid desc
                        ", id, step, Business.SystemConfig.SecondInEveryDay);
            System.Data.DataTable rs1;
            SysParams.OAConnection().RunSql(sql1, out rs1);
            return rs1;
        }

        public string statview_sql1(string step, string type)
        {
            string sql = "SELECT timeout FROM ST_STEP WHERE SName='" + step + "' AND Wid in(SELECT WID FROM ST_WORKFLOW WHERE ROT=0 AND WNAME='" + type + "')";
            string cnys = SysParams.OAConnection().GetValue(sql);
            return cnys;
        }

        public DataTable statview_sql2(string d_s, string d_e, string step, string type)
        {
            string sql = "select count(*),";
            sql += "trunc(sum(exusedtime)/count(*))";

            
            sql += "  from  sv_work_step ";
            sql += " where iid in (select distinct iid from st_instance where wid in (select wid from st_workflow where wname = '" + type + "' and status=2)";

            if (d_s.Equals("") && d_e.Equals(""))
            {
                sql += ")";
            }
            else
            {
                sql += " and ExBeginMonitor>=to_date('" + d_s + "','YYYY-MM-DD') and ExBeginMonitor<=to_date('" + d_e + "','YYYY-MM-DD'))";
            }

            sql += " and step='" + step + "'";

            System.Data.DataTable rs1;
            SysParams.OAConnection().RunSql(sql, out rs1);
            return rs1;
        }

        public DataTable statview_sql3(string d_s, string d_e, string type)
        {
            System.Data.DataTable rs;
            string sql = string.Empty;
            
            sql = "select step from st_work_item_hist ";
            sql += " where iid in (select distinct iid from st_instance where status=2 and rownum=1 and wid in (select wid from st_workflow where wname = '" + type + "')";

            if (d_s.Equals("") && d_e.Equals(""))
            {
                sql += ")";
            }
            else
            {
                sql += " and ExBeginMonitor>=to_date('" + d_s + "','YYYY-MM-DD') and ExBeginMonitor<=to_date('" + d_e + "','YYYY-MM-DD'))";
            }
            sql += " order by wiid ";

            SysParams.OAConnection().RunSql(sql, out rs);
            return rs;
        }

        public DataTable suspend_query_list(string sql, string userid, string flag, bool ISsystemUser)
        {
            //判断当前登录用户是否为系统管理员

            
            if (flag == "ygq") //已挂起
            {
                sql = "select s.serial,s.handleid,s.suspend_type,s.userid,apply_time,s.memo" +
                 ",id,b.wname from xt_suspend_apply s,  st_instance a, st_workflow b  where a.wid = b.wid " +
                 "and a.iid = s.serial  and s.ischeckup ='1' and s.isdelete is null and a.isdelete <>1";
            }
            else if (flag == "nogq")
            {
                sql = "select s.serial,s.handleid,s.suspend_type,s.userid,apply_time,s.memo" +
                     ",id,b.wname from xt_suspend_apply s,  st_instance a, st_workflow b  where a.wid = b.wid " +
                     "and a.iid = s.serial  and s.ischeckup is null and s.isdelete is null and a.isdelete <>1";
            }
            else
            {
                sql = "select s.serial,s.handleid,s.suspend_type,s.userid,apply_time,s.memo" +
                  ",id,b.wname from xt_suspend_apply s,  st_instance a, st_workflow b  where a.wid = b.wid " +
                  "and a.iid = s.serial  and s.ischeckup ='1' and s.isdelete is null and a.isdelete <>1";
            }

            //***************************************************//

            if (!ISsystemUser)//非系统管理员
            {
                sql += " and s.nextno in (" +
                      "select distinct a.FLOW_NO from XT_SUSPEND_FLOW a,XT_SUSPENDFLOW_USERID b where a.ID=b.FLOW_ID and b.USERID='" + userid + "')" +
                      " and a.wid in(select SERIAL_ID from XT_SUSPENDFLOW_USERID where userid='" + userid + "')";
            }

            System.Data.DataTable dtOut;
            SysParams.OAConnection().RunSql(sql, out dtOut);
            return dtOut;
        }
    }
}
